package com.ingenieria.industrial.dao.implementacion;

import com.ingenieria.industrial.dao.diseño.IEventoDAO;
import com.ingenieria.industrial.dto.Documento;
import com.ingenieria.industrial.dto.Evento;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.joda.time.DateTime;

public class EventoDAO extends BaseDAO implements IEventoDAO {

    public EventoDAO(Connection cnx) {
        super(cnx);
    }

    @Override
    public int insert(Evento evento) throws SQLException {
        String max = null;
        int maximo = 0;
        String codigo = null;

        String sql0 = "select max(substring(cod_eve,3)) as maximo from t_eventos";

        PreparedStatement ps0 = cnx.prepareStatement(sql0);
        ResultSet rs0 = ps0.executeQuery();
        if (rs0.next()) {
            max = rs0.getString("maximo");
        }

        if (isVacio(max)) {
            max = "0";
        }

        maximo = toInt(max) + 1;

        codigo = "E" + rellenarCadIzquierda(convCadena(maximo), 3, '0');

        int results = -1;
        String sqlQuery = "INSERT INTO t_eventos(cod_eve, nombre, descrip, estado, usu_reg, fec_reg) "
                + " VALUES(?, ?, ?, ?, ?, ?); ";
        PreparedStatement ps = cnx.prepareStatement(sqlQuery);
        int pos = 1;
        ps.setString(pos++, codigo);
        ps.setString(pos++, evento.getNombre());
        ps.setString(pos++, evento.getDescrip());
        ps.setString(pos++, evento.getEstado());
        ps.setString(pos++, evento.getUsu_reg());
        ps.setTimestamp(pos++, toTimestampSQL(DateTime.now()));
        results = ps.executeUpdate();

        String sqlQuery2 = "INSERT INTO t_documentos( tip_doc, codigo, documento, usu_reg, fec_reg, nom_arch) "
                + " VALUES(?, ?, ?, ?, ?, ?); ";
        PreparedStatement ps2 = cnx.prepareStatement(sqlQuery2);
        pos = 1;
        ps2.setString(pos++, "E");
        ps2.setString(pos++, codigo);
        try {
            ps2.setBinaryStream(pos++, evento.getFileData().getInputStream(), evento.getFileData().getFileItem().getSize());
        } catch (IOException ex) {
            Logger.getLogger(EventoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        ps2.setString(pos++, evento.getUsu_reg());
        ps2.setTimestamp(pos++, toTimestampSQL(DateTime.now()));
        ps2.setString(pos++, evento.getFileData().getFileItem().getName());
        ps2.executeUpdate();

        ps0.close();
        rs0.close();
        ps.close();
        ps2.close();

        return results;
    }

    @Override
    public int update(Evento evento) throws SQLException {
        int results = -1;
        String sqlQuery = "UPDATE t_eventos "
                + " SET nombre = ?, descrip = ?, estado = ? "
                + " WHERE cod_eve = ? ";
        PreparedStatement ps = cnx.prepareStatement(sqlQuery);
        int pos = 1;
        ps.setString(pos++, evento.getNombre());
        ps.setString(pos++, evento.getDescrip());
        ps.setString(pos++, evento.getEstado());
        ps.setString(pos++, evento.getCod_eve());
        results = ps.executeUpdate();

        Documento documento = null;
        DocumentoDAO doc = new DocumentoDAO(cnx);
        try {
            documento = doc.getByCodEve(evento.getCod_eve());
        } catch (Exception ex) {
        }

        if (documento != null) {
            if (!isVacio(evento.getFileData())) {
                String sqlQueryUpd = "UPDATE t_documentos SET"
                        + " tip_doc = ?, codigo = ?, documento =?, usu_reg = ?, fec_reg = ?, nom_arch = ? "
                        + " WHERE codigo = ?";
                PreparedStatement ps1 = cnx.prepareStatement(sqlQueryUpd);
                int pos1 = 1;
                ps1.setString(pos1++, "E");
                ps1.setString(pos1++, evento.getCod_eve());
                try {
                    ps1.setBinaryStream(pos1++, evento.getFileData().getInputStream(), evento.getFileData().getFileItem().getSize());
                } catch (IOException ex) {
                    Logger.getLogger(EventoDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                ps1.setString(pos1++, evento.getUsu_reg());
                ps1.setTimestamp(pos1++, toTimestampSQL(DateTime.now()));
                ps1.setString(pos1++, evento.getFileData().getFileItem().getName());
                ps1.setString(pos1++, evento.getCod_eve());
                ps1.executeUpdate();
                ps1.close();
            }
        } else {
            if (!isVacio(evento.getFileData())) {
                String sqlQuery2 = "INSERT INTO t_documentos( tip_doc, codigo, documento, usu_reg, fec_reg, nom_arch) "
                        + " VALUES(?, ?, ?, ?, ?, ?); ";
                PreparedStatement ps2 = cnx.prepareStatement(sqlQuery2);
                pos = 1;
                ps2.setString(pos++, "E");
                ps2.setString(pos++, evento.getCod_eve());
                try {
                    ps2.setBinaryStream(pos++, evento.getFileData().getInputStream(), evento.getFileData().getFileItem().getSize());
                } catch (IOException ex) {
                    Logger.getLogger(EventoDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                ps2.setString(pos++, evento.getUsu_reg());
                ps2.setTimestamp(pos++, toTimestampSQL(DateTime.now()));
                ps2.setString(pos++, evento.getFileData().getFileItem().getName());
                ps2.executeUpdate();
                ps2.close();
            }
        }
        ps.close();
        return results;
    }

    @Override
    public int delete(String cod_eve) throws SQLException {
        int results = -1;
        String sqlQuery = "DELETE FROM t_eventos WHERE cod_eve = ? ";
        PreparedStatement ps = cnx.prepareStatement(sqlQuery);
        ps.setString(1, cod_eve);
        results = ps.executeUpdate();
        ps.close();
        return results;
    }

    @Override
    public ArrayList<Evento> getAll() throws SQLException {
        ArrayList<Evento> lista = null;
        Evento evento = null;
        String sqlQuery = "SELECT p.*, (select documento from t_documentos where codigo = p.cod_eve) as documento,"
                + " (select nom_arch from t_documentos where codigo = p.cod_eve) as nom_arch "
                + " FROM t_eventos p "
                + " ORDER BY p.cod_eve";

        PreparedStatement ps = cnx.prepareStatement(sqlQuery);
        ResultSet rs = ps.executeQuery();
        lista = new ArrayList<>();
        while (rs.next()) {
            evento = new Evento();
            mapearBeanForRS(rs, evento);
            lista.add(evento);
        }
        rs.close();
        ps.close();
        return lista;
    }

    @Override
    public Evento getById(String cod_eve) throws SQLException {
        Evento evento = null;
        String sqlQuery = "SELECT p.*, (select documento from t_documentos where codigo = p.cod_eve) as documento,"
                + " (select nom_arch from t_documentos where codigo = p.cod_eve) as nom_arch "
                + "FROM t_eventos p "
                + " WHERE cod_eve = ? ";

        PreparedStatement ps = cnx.prepareStatement(sqlQuery);
        ps.setString(1, cod_eve);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            evento = new Evento();
            mapearBeanForRS(rs, evento);
            Blob bytesImagen = rs.getBlob("documento");
            if (bytesImagen != null) {
                InputStream is = bytesImagen.getBinaryStream();
                evento.setArchivo(is);
            }
        }
        rs.close();
        ps.close();
        return evento;
    }

}
